package org.example.managementsystem.repository;

import org.example.managementsystem.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.List;
import java.util.Objects;

@Repository
public class PersonDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public PersonDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 更新工号
     */
    public int updateJobNumber(Long id, String jobNumber) {
        String sql = "UPDATE person SET job_number = ? WHERE id = ?";
        return jdbcTemplate.update(sql, jobNumber, id);
    }

    /**
     * 添加人员
     */
    public Long insertPerson(Person person) {
        String sql = "INSERT INTO person (job_number, name, department, entry_date, " +
                "education, major, title, phone, address, profile) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, person.getJobNumber());
            ps.setString(2, person.getName());
            ps.setString(3, person.getDepartment());
            ps.setDate(4, person.getEntryDate() != null ? 
                    Date.valueOf(person.getEntryDate()) : null);
            ps.setString(5, person.getEducation());
            ps.setString(6, person.getMajor());
            ps.setString(7, person.getTitle());
            ps.setString(8, person.getPhone());
            ps.setString(9, person.getAddress());
            ps.setString(10, person.getProfile());
            return ps;
        }, keyHolder);
        
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    /**
     * 更新人员信息
     */
    public int updatePerson(Person person) {
        String sql = "UPDATE person SET job_number = ?, name = ?, department = ?, " +
                "entry_date = ?, education = ?, major = ?, title = ?, phone = ?, " +
                "address = ?, profile = ? WHERE id = ?";
        
        return jdbcTemplate.update(sql,
                person.getJobNumber(),
                person.getName(),
                person.getDepartment(),
                person.getEntryDate() != null ? Date.valueOf(person.getEntryDate()) : null,
                person.getEducation(),
                person.getMajor(),
                person.getTitle(),
                person.getPhone(),
                person.getAddress(),
                person.getProfile(),
                person.getId());
    }

    /**
     * 删除人员
     */
    public int deletePerson(Long id) {
        String sql = "DELETE FROM person WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID查询人员
     */
    public Person getPersonById(Long id) {
        String sql = "SELECT * FROM person WHERE id = ?";
        List<Person> persons = jdbcTemplate.query(sql, new PersonRowMapper(), id);
        return persons.isEmpty() ? null : persons.get(0);
    }

    /**
     * 根据工号查询人员
     */
    public Person getPersonByJobNumber(String jobNumber) {
        String sql = "SELECT * FROM person WHERE job_number = ?";
        List<Person> persons = jdbcTemplate.query(sql, new PersonRowMapper(), jobNumber);
        return persons.isEmpty() ? null : persons.get(0);
    }

    /**
     * 查询所有人员
     */
    public List<Person> getAllPersons() {
        String sql = "SELECT * FROM person";
        return jdbcTemplate.query(sql, new PersonRowMapper());
    }

    /**
     * 根据条件查询人员
     */
    public List<Person> searchPersons(String keyword) {
        String sql = "SELECT * FROM person WHERE job_number LIKE ? " +
                "OR name LIKE ? OR department LIKE ?";
        
        String searchParam = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new PersonRowMapper(), 
                searchParam, searchParam, searchParam);
    }

    /**
     * 人员行映射器
     */
    private static class PersonRowMapper implements RowMapper<Person> {
        @Override
        public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
            Person person = new Person();
            person.setId(rs.getLong("id"));
            person.setJobNumber(rs.getString("job_number"));
            person.setName(rs.getString("name"));
            person.setDepartment(rs.getString("department"));
            
            Date entryDate = rs.getDate("entry_date");
            if (entryDate != null) {
                person.setEntryDate(entryDate.toLocalDate());
            }
            
            person.setEducation(rs.getString("education"));
            person.setMajor(rs.getString("major"));
            person.setTitle(rs.getString("title"));
            person.setPhone(rs.getString("phone"));
            person.setAddress(rs.getString("address"));
            person.setProfile(rs.getString("profile"));
            
            return person;
        }
    }

    /**
     * 批量更新空工号
     * 根据部门自动生成工号
     */
    public int batchUpdateEmptyJobNumbers() {
        String sql = "UPDATE person SET job_number = " +
                "CASE department " +
                "WHEN '表面处理车间' THEN CONCAT('BM', id) " +
                "WHEN '锻压车间' THEN CONCAT('DY', id) " +
                "WHEN '组装车间' THEN CONCAT('ZZ', id) " +
                "WHEN '质检车间' THEN CONCAT('ZJ', id) " +
                "WHEN '机加车间' THEN CONCAT('JJ', id) " +
                "ELSE CONCAT('QT', id) " +
                "END " +
                "WHERE job_number IS NULL OR job_number = ''";
        
        return jdbcTemplate.update(sql);
    }
}
